package oas.db.jdbc;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import oas.db.WorkcellRepository;
import oas.domain.Workcell;
import oas.web.PaginationSupport;

/**
 * 部门DAO实现类
 *
 * @author 97496
 * @version v1.1.0
 */
@Repository
public class JdbcWorkcellRepository implements WorkcellRepository {

    private static JdbcTemplate jdbcTemplate;

    @Autowired

    /**
     * 注入jdbcTemplate
     *
     * @param jdbcTemplate jdbcTemplate
     */
    public JdbcWorkcellRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 通过id查找部门
     */
    public Workcell findById(Long id) {
        Workcell workcell = null;
        try {
            workcell = jdbcTemplate.queryForObject(SELECT_WORKCELL + " where id = ? and available=1", new WorkcellRowMapper(), id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return workcell;
    }
    
    /**
     * 通过id查找父部门
     */
    @Override
    public Workcell findParentById(Long id) {
        Workcell workcell = null;
        try {
            workcell = jdbcTemplate.queryForObject("select id,name,available "
            		+ "from workcell" + " where id = (select parentWorkcellId "
            		+ " from workcellRelation where childWorkcellId =? ) and available=1", new WorkcellRowMapper(), id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return workcell;
    }

    /**
     * 添加部门
     * @param workcell
     * @param parentWorkcell
     * @return
     */
    public Workcell addWorkcell(Workcell workcell, Workcell parentWorkcell) {
        jdbcTemplate.update(ADD_WORKCELL, workcell.getName(), workcell.isAvailable());
        String name = workcell.getName();
        workcell = findByName(name);
        jdbcTemplate.update(ADD_WORKCELLRELATION, parentWorkcell.getId(), workcell.getId());
        return workcell;
    }

    /**
     * 删除部门
     * @param workcell
     */
    public void deleteWorkcell(Workcell workcell) {
        Long id = workcell.getId();
        jdbcTemplate.update(UPDATE_AVAILABLE, id);
    }

    /**
     * 更新部门信息
     * @param workcell
     */
    public void updateWorkcell(Workcell workcell) {
        Long id = workcell.getId();
        String name = workcell.getName();
        jdbcTemplate.update(UPDATE_NAME, name, id);
    }

    /**
     * 通过name查找部门
     *
     * @param name
     * @return
     */
    public Workcell findByName(String name) {
        Workcell workcell = null;
        try {
            workcell = jdbcTemplate.queryForObject(SELECT_WORKCELL + " where name = ? and available =1", new WorkcellRowMapper(), name);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return workcell;
    }

    /**
     * 查询部门及子部门
     *
     * @param workcell 跟部门
     * @return 部门列表
     */
    public List<Workcell> findWorkcells(Workcell workcell) {
        Long id = workcell.getId();
        List<Workcell> workcells = jdbcTemplate.query(SELECT_WORKCELL + " where id in "
                + "(select childWorkcellId from workcellRelation where parentWorkcellId= ?) and available =1", new WorkcellRowMapper2(), id);
        return workcells;
        //return findById(workcell.getId()).getChildWorkcells();
    }

    /**
     * 分页查找子部门
     * @param workcell
     * @param pageNo
     * @param pageSize
     * @return
     */
    public PaginationSupport<Workcell> findWorkcells(Workcell workcell, int pageNo, int pageSize) {
        Long id = workcell.getId();
        List<Workcell> workcells = findWorkcells(workcell);
        int totalCount = workcells.size();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<Workcell>(new ArrayList<Workcell>(0), 0);
        }

        List<Workcell> items = jdbcTemplate.query(SELECT_WORKCELL
                        + " where id in (select childWorkcellId from workcellRelation where parentWorkcellId= ?) and available =1"
                        + " order by workcell.id asc limit ? offset ?",
                new WorkcellRowMapper2(), id, pageSize, startIndex);

        return new PaginationSupport<Workcell>(items, totalCount, pageSize, startIndex);
    }


    /**
     * 使用RowMapper处理从数据库中读取的部门信息
     */
    private static class WorkcellRowMapper implements RowMapper<Workcell> {
        public Workcell mapRow(ResultSet resultSet, int i) throws SQLException {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            boolean available = resultSet.getBoolean("available");
            List<Workcell> workcells = jdbcTemplate.query(SELECT_WORKCELL + " where id in (select childWorkcellId from workcellRelation where parentWorkcellId= ?) and available =1", new WorkcellRowMapper2(), id);
            return new Workcell(id, name, available, workcells);
        }
    }

    /**
     * 使用RowMapper处理从数据库中读取的部门信息
     */
    private static class WorkcellRowMapper2 implements RowMapper<Workcell> {
        public Workcell mapRow(ResultSet resultSet, int i) throws SQLException {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            boolean available = resultSet.getBoolean("available");
            //List<Workcell> workcells=jdbcTemplate.query(SELECT_WORKCELL+" where id=(select childWorkcellId from workcellRelation where id= ?",new WorkcellRowMapper2(),id);
            return new Workcell(id, name, available, null);
        }
    }

    private static final String SELECT_WORKCELL = "select id,name,available from workcell ";
    private static final String ADD_WORKCELL = "insert into workcell ( name, available) " + "values ( ?, ?) ";
    private static final String ADD_WORKCELLRELATION = "insert into workcellRelation(parentWorkcellId,childWorkcellId) values(?,?)";
    private static final String UPDATE_AVAILABLE = "update workcell set available = 0 where id =?";
    private static final String UPDATE_NAME = "update workcell set name=? where id=?";
}
